Imports System.Windows.Forms

Public Class frmChangeRecords

    Public Enum enumChangeDelete
        Change
        Delete
    End Enum

    Private TableName As String
    Private ChangeDelete As enumChangeDelete
    Private dt As DataTable 'for the grid

    Friend Sub New(ByVal _TableName As String, ByVal _ChangeDelete As enumChangeDelete)
        InitializeComponent()
        TableName = _TableName
        ChangeDelete = _ChangeDelete
    End Sub

    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        If RunSQL() Then RefreshAllForms()
        If ChangeDelete = enumChangeDelete.Delete Then
            'see if all records have been deleted; if so, close form
            Dim NumRec As Object = Project.DB.ExecuteScalar("SELECT COUNT(*) FROM {0}", Project.FullTableName(TableName))
            If NumRec IsNot Nothing AndAlso NumRec = 0 Then Close()
        End If
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.DialogResult = System.Windows.Forms.DialogResult.Cancel
        Me.Close()
    End Sub

    Private Sub frmChangeRecords_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        SaveSetting(REGAPP, Name, cboCopyOption.Name, cboCopyOption.SelectedIndex)
        SaveSetting(REGAPP, Name, chkAvail.Name, chkAvail.Checked)
        SaveSetting(REGAPP, Name, optPriorSelection.Name, optPriorSelection.Checked)
        SaveSetting(REGAPP, Name, optNewSelection.Name, optNewSelection.Checked)
    End Sub

    Private Sub frmChangeRecords_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            chkAvail.Checked = GetSetting(REGAPP, Name, chkAvail.Name, False)
            dt = New DataTable
            dt.Columns.Add(NewColumn("Field_Name", "Field Name", GetType(String), 100))
            dt.Columns.Add(NewColumn("Where", "Selection Criteria", GetType(String), 10000))
            Dim ChangeDeleteText As String = [Enum].GetName(GetType(enumChangeDelete), ChangeDelete)
            Text = String.Format("{0} Selected Records in {1}", ChangeDeleteText, ActiveTableName)
            optPriorSelection.Text = String.Format("&{0} {1:N0} records that were previously selected", ChangeDeleteText, Project.DB.GetRecordCount(clsProject.TempData))
            If ChangeDelete = enumChangeDelete.Change Then
                dt.Columns.Add(NewColumn("New_Value", "New Value", GetType(String), 255))
                btnHint.Visible = True
                cboCopyOption.Visible = True
            Else
                btnHint.Visible = False
                cboCopyOption.Visible = False
            End If
            For Each c As DataColumn In Project.GetTableTemplate(TableName).Columns
                If ChangeDelete = enumChangeDelete.Change Then dt.Rows.Add(Proper(c.ColumnName), "", "") Else dt.Rows.Add(c.ColumnName, "")
            Next
            With dgv
                .DataSource = dt
                .RowHeadersVisible = False
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToResizeColumns = True
                .AllowUserToResizeRows = False
                .EditMode = DataGridViewEditMode.EditOnKeystrokeOrF2
                For c As Integer = 0 To .ColumnCount - 1
                    With .Columns(c)
                        .AutoSizeMode = Choose(c + 1, DataGridViewAutoSizeColumnMode.AllCells, DataGridViewAutoSizeColumnMode.Fill, DataGridViewAutoSizeColumnMode.Fill)
                        .ReadOnly = IIf(c = 0, True, False)
                        .SortMode = DataGridViewColumnSortMode.NotSortable
                        .HeaderText = dt.Columns(c).Caption
                    End With
                Next
                With .Columns(0)
                    .ReadOnly = True
                    .DefaultCellStyle.BackColor = SystemColors.ButtonFace
                End With
                Dim dgvBtn As New DataGridViewButtonColumn
                With dgvBtn
                    .Text = "Assist"
                    .HeaderText = ""
                    .UseColumnTextForButtonValue = True
                    .AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
                End With
                .Columns.Add(dgvBtn)
                .CurrentCell = .Item(1, 0)
            End With

            cboCopyOption.SelectedIndex = GetSetting(REGAPP, Name, cboCopyOption.Name, 0)

            Dim Reason As String = ""
            If MainForm.IsSelectionCurrent(TableName, Reason) Then
                optPriorSelection.Checked = GetSetting(REGAPP, Name, optPriorSelection.Name, True)
                optNewSelection.Checked = GetSetting(REGAPP, Name, optNewSelection.Name, False)
            Else
                optPriorSelection.Enabled = False
                optPriorSelection.Text = Reason
                optNewSelection.Checked = True
            End If
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Function RunSQL() As Boolean
        Try
            Dim SQLSelect As String = "", SQLUpdate As String = "", SQLInsert1 As String = "", SQLInsert2 As String = ""
            BuildSQL(SQLSelect, SQLUpdate, SQLInsert1, SQLInsert2)
            If Not Project.ResetPDox(TableName) Or SQLSelect = "" Then Return False
            Dim NumRec As Object = Project.DB.ExecuteScalar(SQLSelect)
            If NumRec Is Nothing OrElse NumRec < 0 Then Return False
            If NumRec = 0 Then
                MessageBox.Show("No records match the criteria you specified.", Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                Return False
            Else
                If MessageBox.Show(String.Format("You are about to {0} {1} records. This operation cannot be undone. Are you sure you want to continue?",
                                                  IIf(ChangeDelete = enumChangeDelete.Delete OrElse cboCopyOption.SelectedIndex = 0, "", "append and ") & IIf(ChangeDelete = enumChangeDelete.Change, "change", "delete"), CType(NumRec, Integer)),
                                             Me.Text, MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.No Then Return False
            End If

            If ChangeDelete = enumChangeDelete.Change Then
                If cboCopyOption.SelectedIndex = 0 Then
                    NumRec = Project.DB.ExecuteNonQuery(SQLUpdate)
                Else
                    If Project.DB.ExecuteNonQuery(SQLInsert1) <> -2 AndAlso Project.DB.ExecuteNonQuery(SQLUpdate) <> -2 Then
                        NumRec = Project.DB.ExecuteNonQuery(SQLInsert2)
                    End If
                End If
            Else
                NumRec = Project.DB.ExecuteNonQuery(SQLUpdate)
            End If
            IniFile.SetKey(clsWRDBIniFile.enumSection.Application, clsWRDBIniFile.enumKey.AppLastEditTime, Now.ToString(System.Globalization.CultureInfo.InvariantCulture.DateTimeFormat))

            If NumRec > 0 And Not MainWRDB.ExpertMode Then MessageBox.Show(String.Format("A total of {0} records were {1}.", NumRec, IIf(ChangeDelete = enumChangeDelete.Change, "changed", "deleted")), Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)

            If ChangeDelete = enumChangeDelete.Delete And optPriorSelection.Checked Then Close()

            Project.UpdateTrackingRecord(TableName, Project.DB.GetRecordCount(TableName), True)

        Catch ex As DbSqlException
            WarningMsg(ex.Message)
        Catch ex As Exception
            ErrorMsg(, ex)
        Finally
            IniFile.Save()
        End Try
        Return True
    End Function

    Private Sub BuildSQL(Optional ByRef SQLSelect As String = "", Optional ByRef SQLUpdate As String = "", Optional ByRef SQLInsert1 As String = "", Optional ByRef SQLInsert2 As String = "")
        Try
            Dim SQLWhere As String = "", SQLSet As String = ""
            If optPriorSelection.Checked Then
                Dim LastSQL As String = IniFile.GetKey(clsWRDBIniFile.enumSection.Application, clsWRDBIniFile.enumKey.AppLastQuerySQL, "").Replace("Dta.", "")
                If LastSQL.ToUpper.Contains("WHERE ") Then SQLWhere = LastSQL.Substring(LastSQL.ToUpper.IndexOf("WHERE ") + 6)
                If ChangeDelete = enumChangeDelete.Change Then
                    For r As Integer = 0 To dt.Rows.Count - 1
                        Dim DataType As System.Type = Project.GetTableTemplate(TableName).Columns(r).DataType
                        With dt.Rows(r)
                            Dim FieldName As String = .Item("Field_Name").ToString
                            Dim NewValue As String = .Item("New_Value").ToString
                            If NewValue <> "" Then SQLSet &= IIf(SQLSet = "", " ", ", ") & StringFormat("{0} = {1}", FieldName, NewValue)
                        End With
                    Next
                End If
            Else
                For r As Integer = 0 To dt.Rows.Count - 1
                    Dim DataType As System.Type = Project.GetTableTemplate(TableName).Columns(r).DataType

                    With dt.Rows(r)
                        Dim FieldName As String = .Item("Field_Name").ToString
                        If ChangeDelete = enumChangeDelete.Change Then
                            Dim NewValue As String = .Item("New_Value").ToString
                            Select Case FieldName.ToUpper
                                Case "STATION_ID", "PCODE", "CCODE", ""
                            End Select
                            If NewValue <> "" Then SQLSet &= IIf(SQLSet = "", " ", ", ") & StringFormat("{0} = {1}", FieldName, NewValue)
                        End If
                        Dim Where As String = .Item("Where").ToString
                        If Where <> "" Then
                            SQLWhere &= StringFormat("{0} {1} {2}", IIf(SQLWhere = "", "", " AND"), IIf(Where.ToUpper.Contains(FieldName.ToUpper), "", FieldName), Where)
                        End If
                    End With
                Next
            End If
            If Not Project.ResetPDox(TableName) Then Exit Sub
            SQLSelect = StringFormat("SELECT COUNT(*) FROM {0} {1}", Project.FullTableName(TableName), IIf(SQLWhere = "", "", " WHERE " & SQLWhere))
            If ChangeDelete = enumChangeDelete.Change Then
                If SQLSet = "" Then WarningMsg("You must enter new values for at least one field.") : SQLSelect = "" : SQLUpdate = "" : Exit Sub
                'update CREATE_UPDATE field too
                SQLSet &= ", Create_Update=" & Project.DB.DateTime(Now)
                If cboCopyOption.SelectedIndex = 0 Then 'update in place
                    SQLUpdate = StringFormat("UPDATE {0} SET {1}{2}", Project.FullTableName(TableName), SQLSet, IIf(SQLWhere = "", "", " WHERE " & SQLWhere))
                Else 'select data and export to temporary table, then update, then append
                    Dim TempName As String = clsProject.TempPrefix & "CopyUpdate"
                    Project.DB.DropTable(TempName)
                    If Project.DB.CreateTable(TempName, Project.GetTableTemplate(clsProject.enumTableTypes.Temporary)) Then
                        SQLInsert1 = String.Format("INSERT INTO {0} SELECT * FROM {1} {2}", TempName, Project.FullTableName(TableName), IIf(SQLWhere = "", "", " WHERE " & SQLWhere))
                        SQLUpdate = String.Format("UPDATE {0} SET {1}", TempName, SQLSet)
                        SQLInsert2 = String.Format("INSERT INTO {0} SELECT * FROM {1}", Project.FullTableName(TableName), TempName)
                    End If
                End If
            Else
                SQLUpdate = StringFormat("DELETE FROM {0} {1}", Project.FullTableName(TableName), IIf(SQLWhere = "", "", " WHERE " & SQLWhere))
            End If
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub DataGridView1_CellContentClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv.CellContentClick
        Try
            If e.ColumnIndex <> dgv.ColumnCount - 1 Then Exit Sub
            Dim AssistType As frmQueryAssist.enumAssist
            Select Case dgv.Item(0, e.RowIndex).Value.ToString.ToUpper
                Case "STATION_ID" : AssistType = frmQueryAssist.enumAssist.StationID
                Case "PCODE" : AssistType = frmQueryAssist.enumAssist.PCode
                Case "CCODE" : AssistType = frmQueryAssist.enumAssist.CCode
                Case "RCODE" : AssistType = frmQueryAssist.enumAssist.RCode
                Case "QCODE" : AssistType = frmQueryAssist.enumAssist.QCode
                Case "AGENCY" : AssistType = frmQueryAssist.enumAssist.Agency
                Case "SCODE", "LCODE" : AssistType = frmQueryAssist.enumAssist.Validate
                Case Else
                    If Project.GetTableTemplate(TableName).Columns(e.RowIndex).DataType Is GetType(Date) Then
                        AssistType = frmQueryAssist.enumAssist.DateTimeValue
                    ElseIf Project.GetTableTemplate(TableName).Columns(e.RowIndex).DataType Is GetType(String) Then
                        AssistType = frmQueryAssist.enumAssist.StringValue
                    Else
                        AssistType = frmQueryAssist.enumAssist.RealValue
                    End If
            End Select
            With New frmQueryAssist(Project.DB.FullTableName(TableName), dgv.Item(0, e.RowIndex).Value.ToString, AssistType)
                .chkAvail.Checked = chkAvail.Checked
                If .ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                    Dim s As String = .QueryString
                    Dim maxlen As Integer = CType(dgv.DataSource, DataTable).Columns(1).MaxLength
                    If s.Length > maxlen Then
                        WarningMsgFmt("You have selected too many items to change resulting in large query string ({0}) which exceeds the maximum capacity ({1}). Please revise your selection criteria.", s.Length, maxlen)
                        dgv.Item(1, e.RowIndex).Value = ""
                    Else
                        dgv.Item(1, e.RowIndex).Value = .QueryString
                    End If
                End If
                .Dispose()
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub DataGridView1_CellEnter(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv.CellEnter
        If e.ColumnIndex = 0 Then Me.dgv.Item(1, e.RowIndex).Selected = True
    End Sub

    Private Sub dgv_CellValidated(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv.CellValidated
        Try
            Dim KeyFields As New Generic.List(Of String)
            KeyFields.AddRange(New String() {"STATION_ID", "PCODE", "CCODE", "RCODE", "CONVERT_STATION_ID", "CONVERT_PCODE", "CONVERT_CCODE", "CONVERT_RCODE", "QCODE", "BRANCH_ID", "DS_BRANCH_ID", "STATIONGROUP", "PCODEGROUP", "CONVGROUP", "AGENCY", "STATION_TYPE", "WATERBODYTYPE", "BASIN_NAME", "ECOREGION", "PLANREGION", "STATE"})
            With dgv.Item(e.ColumnIndex, e.RowIndex)
                If .EditedFormattedValue Is DBNull.Value Or .Value.ToString = "" Then Exit Sub
                Dim NewValue As String = .EditedFormattedValue.ToString
                Dim dtData As DataTable = Project.GetTableTemplate(Me.TableName)
                Dim dataCol As DataColumn = dtData.Columns(e.RowIndex)
                Dim FieldName As String = dataCol.ColumnName

                If e.ColumnIndex = 1 Then 'where condition
                    If dataCol.DataType Is GetType(String) Or dataCol.DataType Is GetType(Date) Or dataCol.DataType Is GetType(Double) Or dataCol.DataType Is GetType(Single) Or dataCol.DataType Is GetType(Integer) Then
                        For Each s As String In "='<>%" 'assume is correctly formatted
                            If NewValue.Contains(s) Then Exit Sub
                        Next

                        If NewValue.ToUpper.Contains("NULL") Then Exit Sub
                        If NewValue.ToUpper.StartsWith("IN") Then Exit Sub
                        If NewValue.ToUpper.StartsWith("LIKE") Then
                            .Value = NewValue.Replace("*", "%")
                            Exit Sub
                        End If
                        Dim Value As String = ""
                        If NewValue.Contains(",") Then
                            For Each s As String In NewValue.Split(",")
                                If Value <> "" Then Value &= ","
                                If dataCol.DataType Is GetType(String) Or dataCol.DataType Is GetType(Date) Then
                                    Value &= StringFormat("'{0}'", s)
                                Else
                                    Value &= StringFormat("{0}", s)
                                End If
                            Next
                            .Value = StringFormat("In ({0})", Value)

                        ElseIf NewValue.Contains("*") And dataCol.DataType Is GetType(String) Then
                            .Value = StringFormat("LIKE '{0}'", NewValue.Replace("*", "%"))

                        Else
                            If dataCol.DataType Is GetType(String) Then
                                .Value = StringFormat("= '{0}'", NewValue)
                            ElseIf dataCol.DataType Is GetType(Date) Then
                                .Value = StringFormat("= {0}", Project.DB.DateTime(NewValue))
                            Else
                                .Value = StringFormat("= {0}", NewValue)
                            End If
                        End If
                    End If
                ElseIf e.ColumnIndex = 2 And ChangeDelete = enumChangeDelete.Change Then 'is Set column; just add ' if necessary but don't prefix with =; is contains field name assumes is expression and is formatted correctly

                    If NewValue.Contains("'") Or NewValue.ToUpper.Contains("NULL") Then Exit Sub 'assume is correctly formatted

                    If KeyFields.Contains(FieldName.ToUpper) Then NewValue = NewValue.ToUpper

                    If NewValue.Length >= 1 AndAlso "+-*/".Contains(NewValue.Substring(0, 1)) Then 'try to help with formatting

                        If dataCol.DataType Is GetType(String) And "+".Contains(NewValue.Substring(0, 1)) Then
                            .Value = String.Format("{0} {1} '{2}'", FieldName, Project.DB.Concat, NewValue.Substring(1).Trim.Replace("'", ""))
                            Exit Sub

                        ElseIf (dataCol.DataType Is GetType(Single) Or dataCol.DataType Is GetType(Double)) And "+-*/".Contains(NewValue.Substring(0, 1)) Then
                            .Value = String.Format("{0} {1} {2}", FieldName, NewValue.Substring(0, 1), Val(NewValue.Substring(1)))
                            Exit Sub

                        ElseIf dataCol.DataType Is GetType(Date) And "+-".Contains(NewValue.Substring(0, 1)) Then
                            Dim Sign As String = NewValue.Substring(0, 1)
                            Dim NewVal As Single = Val(NewValue.Substring(1))
                            If NewVal = 0 Then Exit Sub
                            Dim Interval As String = NewValue.ToUpper.Replace("S", "")
                            If Interval.EndsWith("HOUR") Then Interval = "HOUR"
                            If Interval.EndsWith("DAY") Then Interval = "DAY"
                            If Interval.EndsWith("MONTH") Then Interval = "MONTH"
                            If Interval.EndsWith("YEAR") Then Interval = "YEAR"
                            Select Case Project.DB.DatabaseType
                                Case clsDB.enumDBType.Access
                                    Select Case Interval
                                        Case "HOUR" : Interval = "h"
                                        Case "DAY" : Interval = "d"
                                        Case "MONTH" : Interval = "m"
                                        Case "YEAR" : Interval = "yyyy"
                                        Case Else
                                            Exit Sub
                                    End Select
                                    .Value = String.Format("DATEADD(""{0}"",{1}{2},{3})", Interval, Sign, NewVal, FieldName)
                                Case clsDB.enumDBType.SQL_Server, clsDB.enumDBType.SQL_Server_CE
                                    Select Case Interval
                                        Case "HOUR" : Interval = "hh"
                                        Case "DAY" : Interval = "dd"
                                        Case "MONTH" : Interval = "mm"
                                        Case "YEAR" : Interval = "yyyy"
                                        Case Else
                                            Exit Sub
                                    End Select
                                    .Value = String.Format("DATEADD(""{0}"",{1}{2},{3})", Interval, Sign, NewVal, FieldName)
                                Case clsDB.enumDBType.MySQL
                                    .Value = String.Format("DATE_{2}({0}, INTERVAL {1} {3})", FieldName, NewVal, IIf(Sign = "+", "ADD", "SUB"), Interval)
                                Case clsDB.enumDBType.SQLite
                                    .Value = String.Format("DATETIME({0},'{1}{2} {3}S')", FieldName, Sign, NewVal, Interval)
                                Case Else
                                    Select Case Interval
                                        Case "HOUR" : Interval = "/24.0000000"
                                        Case "DAY" : Interval = ""
                                        Case "MONTH" : Interval = "*30.0000000"
                                        Case "YEAR" : Interval = "*365.0000000"
                                        Case Else
                                            Exit Sub
                                    End Select
                                    Dim NewValStr As String = NewVal
                                    If Not NewValStr.Contains(".") Then NewValStr &= ".000000"
                                    .Value = String.Format("{0}{1}{2}{3}", FieldName, Sign, NewValStr, Interval)
                            End Select
                            Exit Sub
                        End If
                        End If

                        'following probably won't be used due to enhancements above
                        If dataCol.DataType Is GetType(String) And _
                           Not NewValue.ToUpper.Contains(dgv.Item(0, e.RowIndex).EditedFormattedValue.ToString.ToUpper) Then 'use quotes for strings and dates
                            .Value = StringFormat("'{0}'", NewValue)
                        ElseIf dataCol.DataType Is GetType(Date) Then
                            .Value = Project.DB.DateTime(NewValue)
                        ElseIf dataCol.DataType Is GetType(String) Then
                            .Value = NewValue.Replace("+", Project.DB.Concat).Replace("||", Project.DB.Concat) 'this forces use of the correct concatenation string for the provider
                        End If
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Function fixLikeWildcards(ByVal s As String) As String
        Dim s2 As String
        s2 = UCase(s)
        If s2 Like "*LIKE*" Then
            s = s.Replace("..", "%")
            s = s.Replace("*", "%")
            s = s.Replace("@", "_")
            s = s.Replace("?", "_")
        End If
        Return s
    End Function

    Private Sub btnShowSQL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowSQL.Click
        Dim SQLUpdate As String = ""
        BuildSQL(, SQLUpdate)
        If SQLUpdate <> "" Then MessageBox.Show(StringFormat(FormatSQL(SQLUpdate)), "Show SQL", MessageBoxButtons.OK)
    End Sub

    Private Sub btnHint_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnHint.Click
        Dim HintText As String = StringFormat(IIf(optNewSelection.Checked, "The Assist buttons help you to build correctly formatted text only for the Selection Criteria column. ", "") &
        "Here are some hints for entering correctly formatted values in the New Value column:\n\n" & _
        "\t\b To overwrite the field's values, just enter the new value (quotes will automatically be added for strings and dates if needed)\n" & _
        "\t\b To modify an existing number, use the field name in a formula (e.g., ""Result * 1.95"")\n" & _
        "\t\b To concatenate to an existing string, use the ""||"" symbol (e.g., ""Station_ID || '_New'"")\n" & _
        "\t\b To modify an existing date, the procedure depends on the type of data provider:\n" & _
        "\t\t- Paradox, Firebird, Oracle: use the field name and add or subtract the number of days (e.g., ""Date_Time - 1/24"" to subtract one hour)\n" & _
        "\t\t- MySQL: use the DATE_ADD and DATE_SUB functions (e.g., ""DATE_SUB(Date_Time,INTERVAL 1 HOUR)"" to subtract one hour)\n" & _
        "\t\t- MS SQL Server and Access: use the DATEADD function (e.g., ""DATEADD(""h"",-1,Date_Time)"" to subtract one hour)\n" & _
        "\t\t- SQLite: use the DATETIME function (e.g., ""DATETIME(Date_Time,'-1 HOUR')"" to subtract one hour)\n" & _
        "\t\b To concatenate to an existing string, use the ""||"" symbol (e.g., ""Station_ID || '_New'"")\n\n" & _
        "To help you WRDB will automatically form the correct SQL syntax if you wish to modify existing values; just type an arithmetic symbol as the first New Value column character:\n\n" & _
        "\t\b Numbers: +,-,*, and / are recognized as an arithmetic operator that will be applied to the following number (e.g., * 1.95)\n" & _
        "\t\b Strings: + is recognized as concatenation operator that will be applied to the following string (quotes will be added) (e.g., + _NEW)\n" & _
        "\t\b Dates: + and - are recognized as addition/subtraction of specified number of hours, days, months, or years (e.g., - 1 hour); must be separated by spaces!")
        MessageBox.Show(HintText, "Change Selected Records Hints", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    ''' <summary>
    ''' Utility function to easily add a column to a data table
    ''' </summary>
    ''' <param name="ColumnName">Name of field</param>
    ''' <param name="Caption">Caption for grid heading</param>
    ''' <param name="DataType">Data type</param>
    ''' <param name="MaxLength">Maximum field length (for strings)</param>
    ''' <returns>Data table column</returns>
    Friend Function NewColumn(ByVal ColumnName As String, ByVal Caption As String, ByVal DataType As System.Type, Optional ByVal MaxLength As Integer = 0) As DataColumn
        Dim col As New DataColumn()
        With col
            .ColumnName = ColumnName
            .Caption = Caption
            .DataType = DataType
            If DataType Is GetType(String) Then .MaxLength = MaxLength
        End With
        Return col
    End Function

    Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
        For r As Integer = 0 To dgv.RowCount - 1
            For c As Integer = 1 To dgv.ColumnCount - 2
                dgv.Item(c, r).Value = Nothing
            Next
        Next
    End Sub

    Private Sub optPriorSelection_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles optPriorSelection.CheckedChanged
        chkAvail.Visible = Not optPriorSelection.Checked
        'btnHint.Visible = Not optPriorSelection.Checked And ChangeDelete = enumChangeDelete.Change
        btnReset.Visible = Not optPriorSelection.Checked
        If ChangeDelete = enumChangeDelete.Change Then
            lblInfo.Visible = True
            dgv.Visible = True
            dgv.Columns(1).Visible = Not optPriorSelection.Checked
            dgv.Columns(3).Visible = Not optPriorSelection.Checked
            If optPriorSelection.Checked Then
                lblInfo.Text = "Enter the new values to be assigned to fields in the selected records. Click the Hint button for assistance."
            Else
                lblInfo.Text = "Enter selection criteria for the records you want to change, then the new values to be assigned to fields in those records; if you enter no criteria, all records will be changed. You can click the 'Assist' buttons for help in creating the criteria; if you display only available values, it will take longer to open the Assist forms."
            End If
        Else
            lblInfo.Visible = Not optPriorSelection.Checked
            dgv.Visible = Not optPriorSelection.Checked
        End If
    End Sub

    Private Sub cboCopyOption_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cboCopyOption.SelectedIndexChanged
        Dim s As String = IIf(ChangeDelete = enumChangeDelete.Delete, "delete", "change")
        Dim selText As String = String.Format(IIf(optPriorSelection.Checked, "You have already selected the records you want to {0}.", "Enter selection criteria for the records you want to {0}; if you enter no criteria, all records will be {0}d. You can click the 'Assist' buttons for help in creating the criteria (if you check 'Display only available values', it will take longer to open the Assist forms)."), s)
        If ChangeDelete = enumChangeDelete.Delete Then
            lblInfo.Text = selText & " Click the 'Apply' button and then confirm the deletion."
        Else
            If cboCopyOption.SelectedIndex = 0 Then
                lblInfo.Text = selText & " Then enter the new values to be assigned to fields in the selected records. Click the 'Apply' button and then confirm the change."
            Else
                lblInfo.Text = selText & " These records will first be duplicated, and then the new values you enter will be assigned to fields in those new records. Click the 'Apply' button and then confirm the change."
            End If
        End If
    End Sub

    Private Sub frmChangeRecords_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown
        Static AlreadyShown As Boolean = False
        If Not AlreadyShown Then frmInfoMsg.ShowInfoMsg(ChangeDelete.ToString & " Records", "Note that you can use this form to identify which records you want to {0} or you can {0} the records you previously identified using one of the data selection tools; just indicate your preference in the Data Selection portion of the form.", ChangeDelete.ToString.ToLower)
        AlreadyShown = True
    End Sub
End Class
